﻿# coding=utf-8
#conding:utf8
__author__ = 'Administrator'
#jk409 update 2015-5-23
import time
#import pymysql
#import pymongo
import sqlite3
import psycopg2
#import redis
class Mysql:
    def __init__(self,host,user,password,db,port):
        self.host = host
        self.user = user
        self.passwd = password
        self.db = db
        self.port = int(port)
        self.charset = 'utf8'

    def read(self,sql,args=()):
        try:
            cnn = pymysql.connect(host=self.host,port=self.port,user=self.user, passwd=self.passwd, db=self.db,charset=self.charset)
            #self.cnn.autocommit(True)
            cur= cnn.cursor()
        except:
            print('数据库连接失败......')
            return False

        try:
            cur.execute(sql,args)
            data = cur.fetchall()
            #字典json格式的
            #data_dict = [dict((self.cur.description[i][0], value) for i, value in enumerate(row)) for row in self.cur.fetchall()]
            data = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in data]
            #print('\n',data_dict)
        except:
            print('数据查询失败！')
            return False
        finally:
            cur.close()
            cnn.close()
        return data

    def write(self,sql,args=()):
        try:
            cnn = pymysql.connect(host=self.host,user=self.user, passwd=self.passwd, db=self.db, charset=self.charset)
            #self.cnn.autocommit(True)
            cur= cnn.cursor()
        except:
            print('数据库连接失败......')
            return False

        try:
            cur.execute(sql,args)
            cnn.commit()
        except:
            cnn.rollback()
            print('数据写入失败！')
            return False
        finally:
            cur.close()
            cnn.close()
        return True

class Sqlite3():
    def __init__(self,db):
        '''
        :param db:
        ：python插入的数据正常
        :客户端工具添加的数据会有编码的问题，查询返回结果的
        字段有汉子的话需要使用gbk读
        字段是数字字母字符串默认读取即可（如下：）
        :utf8 res[0]['tag']
        :gbk res[0]['tag'].decode('gb2312').encode('utf-8')
        '''
        self.db=db

    def read(self,sqls,args):
        try:
            self.cnn = sqlite3.connect(database=self.db)
            self.cnn.text_factory = str
        except:
            print('连接数据%s失败!'%self.db)
            return False
        try:
            self.cur = self.cnn.cursor()
            data=self.cur.execute(sqls,args).fetchall()
            res = [dict((self.cur.description[i][0], value) for i, value in enumerate(row)) for row in data]
        except:
            res= False
            print('数据库读取失败!')
        finally:
            self.cnn.close()
        return  res

    def find(self, args):
        try:
            self.cnn = sqlite3.connect(database=self.db)
            self.cnn.text_factory = str
        except:
            print('连接数据%s失败!' % self.db)
            return False

        try:
            self.cur = self.cnn.cursor()
            data = self.cur.execute(args).fetchone()
            res = [dict((self.cur.description[i][0], value) for i, value in enumerate(row)) for row in data]
        except:
            res=False
            print('数据库读取失败，条件：%s' % args)
        finally:
            self.cnn.close()
        return res

    def write(self, sqls, args):
        try:
            self.cnn = sqlite3.connect(database=self.db)
            self.cnn.text_factory = str
        except:
            print('连接数据%s失败!' % self.db)
            return False

        try:
            #lastrowid:返回ID
            #res=self.cnn.cursor().execute(sqls,args).lastrowid
            self.cnn.cursor().execute(sqls, args)
            self.cnn.commit()
            self.cnn.close()
            return True
        except:
            self.cnn.rollback()
            self.cnn.close()
            print('写入%s数据库失败!' % self.db)
            return False



    #初始化
    def INIT(self):
        print('开始初始化数据库，数据将会清空！')
        self.write('drop table domain','')
        self.write('drop table user', '')
        domain_sql = '''
        create table domain(
            id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            domain varchar(32) NOT NULL,
            ip varchar(15) NOT NULL,
            status int(1),
            create_time int(10),
            update_time int(10)
        );
        '''
        user_sql = '''
            create table user(
                id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                name varchar(16) NOT NULL,
                email varchar(24) UNIQUE NOT NULL,
                passwd varchar(32) NOT NULL,
                status int(1),
                is_active int(1),
                qx int(1),
                create_time int(11),
                update_time int(11)
            )
            '''
        add_domain_data='insert into domain (`domain`,`ip`,`status`,`create_time`) values (?,?,?,?);'
        add_user_data = 'insert into user (`name`,`email`,`passwd`,`status`,`is_active`,`qx`,`create_time`) values (?,?,?,?,?,?,?);'
        print('开始初始化domain表......')
        self.write(domain_sql,'')
        print('开始初始化domain表数据......')
        self.write(add_domain_data, ('test.kkk.com', '192.168.1.9', '0', int(time.time())))

        print('开始初始化user表......')
        self.write(user_sql, '')
        print('开始初始化User表数据......')
        self.write(add_user_data, ('jk409','jk409@qq.com','123456',0,1,1,int(time.time())))

class Postgrep():
    def __init__(self, host, user, password, db, port=5433):
        self.host = host
        self.user = user
        self.passwd = password
        self.db = db
        self.port = port
        #self.charset = 'utf8'

    def read(self, sql, args=()):
        try:
            cnn = psycopg2.connect(host=self.host, port=self.port, user=self.user, password=self.passwd, database=self.db)
            cur = cnn.cursor()
        except:
            print('数据库连接失败......')
            return False

        try:
            cur.execute(sql, args)
            data = cur.fetchall()
            # 字典json格式的
            # data_dict = [dict((self.cur.description[i][0], value) for i, value in enumerate(row)) for row in self.cur.fetchall()]
            data = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in data]
            # print('\n',data_dict)
        except:
            print('数据查询失败！')
            return False
        finally:
            cur.close()
            cnn.close()
        return data

    def write(self, sql, args=()):
        try:
            cnn=psycopg2.connect(host=self.host, port=self.port, user=self.user, password=self.passwd, database=self.db)
            # self.cnn.autocommit(True)
            cur = cnn.cursor()
        except:
            print('数据库连接失败......')
            return False

        try:
            print '----'
            cur.execute(sql, args)
            cnn.commit()
        except:
            cnn.rollback()
            print('数据写入失败！')
            return False
        finally:
            cur.close()
            cnn.close()
        return True

    def INIT(self):

        sqls='''
        DROP TABLE public."user";

        CREATE TABLE public."user"
(
  id serial NOT NULL,
  name character varying(32),
  age integer,
  phone   character varying(12),
  address character varying(64)
  )
WITH (
  OIDS=FALSE
);
ALTER TABLE public."user"
  OWNER TO postgres;
        '''
        add_user='insert into public.user (name,age,phone,address) values (%s,%s,%s,%s)'
        self.write(sqls)
        self.write(add_user,('kkk',28,'13071322539','深圳-南山'))


if __name__ == '__main__':
    #db=Sqlite3('mydns-masq.db')
    #db.INIT()
    #sqls="update domain set status=?,ip=?,domain=? where id=1"
    #print db.write(sqls,(1,'9.9.9.8','域名'))
    #print db.read('select * from domain where id=%s'%1,())
    db = Postgrep('localhost','postgres','123456','kkk',5433)
    db.write('insert into public.user (name,age,phone,address) values (%s,%s,%s,%s)',('kkk',28,'13071322539','深圳-南山'))
    sqls='SELECT * FROM public."user";'
    res=db.read(sqls)
    print  res
    for i in res:
        print i,i.get('address')

